package homework.jdbc.dao;

import homework.jdbc.domain.User;
import homework.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/13.
 */
public class UserDao {
    Connection conn = JDBCUtils.getConnection();
    public List<User> FindAll(){
        List<User> list = new ArrayList<>();
        Statement sta = null;
        ResultSet set = null;
        try{
            sta = conn.createStatement();
            String sql = "select * from user";
            set = sta.executeQuery(sql);
            while (set.next()){
                User user = new User();
                user.setId(set.getInt("Id"));
                user.setUsername(set.getString("username"));
                user.setPassword(set.getString("password"));
                user.setEmail(set.getString("email"));
                user.setPhone(set.getInt("phone"));
                list.add(user);
            }
            return list;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(sta != null){
                try {
                    sta.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (set != null){
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
    public boolean insert(User user){
        String sql = "insert into user(id,username,password,email,phone) values(?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            ps.setString(4,user.getEmail());
            ps.setInt(5,user.getPhone());
            int num = ps.executeUpdate();
            if(num == 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null){
                JDBCUtils.close();
            }
        }
        return false;
    }
    public boolean update(){
        String sql = "update user set username=?,password=?,email=?,phone=? where id =?";
        //id username password,email,phone;
        return false;
    }
    public boolean delete(User user){

        String sql = "delete from user(username,) where name = ?";
        PreparedStatement ps = null;
        try{

            ps = conn.prepareStatement(sql);
            //ps.setString(1,user);
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null){
                JDBCUtils.close();
            }
        }

        //ResultSet set = sta.
        return false;
    }
    public User FindById(int id){
        Connection conn = JDBCUtils.getConnection();
        User user = new User();
        //Statement sta = null;
        //使用预编译的PreparedStatement
        PreparedStatement ps = null;
        ResultSet set = null;
        try {
            //编写sql语句?是占位符
            String sql = "select * from user where id = ?";
            //SQL语句交给ps进行预处理
            ps = conn.prepareStatement(sql);
            //设置sql语句的参数
            ps.setInt(1,id);
            //执行查询
            set = ps.executeQuery();
            //处理结果
            while (set.next()){
                user.setId(set.getInt("Id"));
                user.setUsername(set.getString("username"));
                user.setPassword(set.getString("password"));
                user.setEmail(set.getString("email"));
                user.setPhone(set.getInt("phone"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if (set != null){
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null){
                JDBCUtils.close();
            }
        }
        return null;
    }


}
